// 需要安装：npm install pgsql-ast-parser
import { parse } from 'pgsql-ast-parser';

export interface TableColumn {
  name: string;
  comment: string;
}

export interface ForeignKey {
  column: string;
  referenceTable: string;
  referenceColumn: string;
  comment: string;
}

export interface TableStructure {
  tableName: string;
  comment: string;
  columns: TableColumn[];
  primaryKeys: string[];
  foreignKeys: ForeignKey[];
}

export function parseSQLToERStructureFromAst(sql: string): TableStructure[] {
  try {
    const ast = parse(sql);
    console.log('AST解析结果:', ast);
    
    const tables: TableStructure[] = [];

    for (const stmt of ast as any[]) {
      console.log('处理语句:', stmt);
      
      if (stmt.type !== 'create table') {
        console.log('跳过非CREATE TABLE语句:', stmt.type);
        continue;
      }

      // 检查表名是否存在
      if (!stmt.name || !stmt.name.name) {
        console.error('表名未定义:', stmt);
        continue;
      }

      const tableName = stmt.name.name;
      const tableComment = (stmt as any).options?.comment ?? '';

      const columns: TableColumn[] = [];
      const primaryKeys: string[] = [];
      const foreignKeys: ForeignKey[] = [];

      // 检查列定义是否存在
      if (!stmt.columns || !Array.isArray(stmt.columns)) {
        console.error('列定义未找到:', stmt);
        continue;
      }

      for (const col of stmt.columns as any[]) {
        console.log('处理列:', col);
        
        // 检查列名是否存在
        if (!col.name || !col.name.name) {
          console.error('列名未定义:', col);
          continue;
        }

        const colName = col.name.name;
        const comment = col.constraints?.find((c: any) => c.type === 'comment')?.comment ?? '';
        columns.push({ name: colName, comment: comment ? `'${comment}'` : '' });

        if (col.constraints?.some((c: any) => c.type === 'primary key')) {
          primaryKeys.push(colName);
        }
      }

      // 检查约束是否存在
      if (stmt.constraints && Array.isArray(stmt.constraints)) {
        for (const constraint of stmt.constraints as any[]) {
          console.log('处理约束:', constraint);
          
          if (constraint.type === 'primary key') {
            if (constraint.columns && Array.isArray(constraint.columns)) {
              primaryKeys.push(...constraint.columns.map((c: any) => c.name));
            }
          }

          if (constraint.type === 'foreign key') {
            // 检查外键约束的完整性
            if (!constraint.columns || !constraint.columns[0] || 
                !constraint.references || !constraint.references.table || 
                !constraint.references.columns || !constraint.references.columns[0]) {
              console.error('外键约束格式不完整:', constraint);
              continue;
            }

            foreignKeys.push({
              column: constraint.columns[0].name,
              referenceTable: constraint.references.table.name,
              referenceColumn: constraint.references.columns[0].name,
              comment: constraint.comment ? `'${constraint.comment}'` : ''
            });
          }
        }
      }

      tables.push({
        tableName,
        comment: tableComment ? `'${tableComment}'` : '',
        columns,
        primaryKeys,
        foreignKeys
      });
    }

    console.log('解析结果:', tables);
    return tables;
  } catch (error) {
    console.error('SQL解析错误:', error);
    console.error('原始SQL:', sql);
    throw new Error(`SQL解析失败: ${error.message}`);
  }
}
